﻿using System;
using System.Text;

namespace System.Data
{
    /// <summary>
    /// 数据库帮助类
    /// </summary>
    public class DbHelper
    {
        #region Properties

        /// <summary>
        /// 获取数据库连接字符串
        /// </summary>
        public string ConnectionString { get; set; }

        /// <summary>
        /// 获取数据库连接对象
        /// </summary>
        public System.Data.IDbConnection Connection { get; protected set; }

        /// <summary>
        /// 获取数据库事务
        /// </summary>
        public System.Data.IDbTransaction Transaction { get; protected set; }

        /// <summary>
        /// 获取数据库工厂
        /// </summary>
        public System.Data.DbFactory Factory { get; private set; }

        #endregion

        #region DbHelper

        private DbHelper() { }

        /// <summary>
        /// 初始化
        /// </summary>
        /// <param name="dbType"> 数据库类型 </param>
        /// <param name="connectionString"> 数据库连接字符串 </param>
        public DbHelper(System.Data.DatabaseType dbType, string connectionString)
        {
            this.Factory = new System.Data.DbFactory(dbType);
            this.Connection = this.Factory.CreateConnection((this.ConnectionString = connectionString));
        }

        /// <summary>
        /// 初始化
        /// </summary>
        /// <param name="dbType"> 数据库类型 </param>
        /// <param name="connectionString"> 数据库连接字符串 </param>
        public DbHelper(string dbType, string connectionString)
        {
            this.Factory = new System.Data.DbFactory(System.Data.DbFactory.GetDatabase(dbType));
            this.Connection = this.Factory.CreateConnection((this.ConnectionString = connectionString));
        }

        /// <summary>
        /// 初始化
        /// </summary>
        /// <param name="dbType"> 数据库类型 </param>
        /// <param name="connection"> 数据库连接 </param>
        /// <param name="transaction"> 数据库事务 </param>
        public DbHelper(System.Data.DatabaseType dbType, System.Data.IDbConnection connection, System.Data.IDbTransaction transaction = null)
        {
            this.Factory = new System.Data.DbFactory(dbType);
            this.Connection = connection;
            this.ConnectionString = connection != null ? connection.ConnectionString : string.Empty;
        }

        /// <summary>
        /// 初始化
        /// </summary>
        /// <param name="dbType"> 数据库类型 </param>
        /// <param name="connection"> 数据库连接 </param>
        /// <param name="transaction"> 数据库事务 </param>
        public DbHelper(string dbType, System.Data.IDbConnection connection, System.Data.IDbTransaction transaction = null)
        {
            this.Factory = new System.Data.DbFactory(System.Data.DbFactory.GetDatabase(dbType));
            this.Connection = connection;
            this.ConnectionString = connection != null ? connection.ConnectionString : string.Empty;
        }

        #endregion

        #region ExecuteNonQuery(执行 SQL 语句，返回受影响行数)

        /// <summary>
        /// 执行 SQL 语句，返回受影响行数
        /// </summary>
        /// <param name="cmdText"> 命令文本 </param>
        /// <param name="cmdType"> 命令类型 </param>
        /// <param name="parameters"> 参数列表 </param>
        /// <returns></returns>
        public int ExecuteNonQuery(string cmdText, CommandType cmdType = CommandType.Text, params IDataParameter[] parameters)
        {
            using (var cmd = this.Factory.CreateCommand())
            {
                //  准备参数信息
                CommandInfo cmdInfo = CommandInfo.Create(cmdType, cmdText, parameters);

                //  更新参数信息
                cmdInfo.UpdateCommand(cmd, this.Connection, this.Transaction);

                //  执行SQL语句
                return cmd.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// 执行 SQL 语句，返回受影响行数
        /// </summary>
        /// <param name="format"> 复合格式字符串 </param>
        /// <param name="args"> 一个对象数组，其中包含零个或多个要设置格式的对象 </param>
        /// <returns></returns>
        public int ExecuteNonQueryFormat(string format, params object[] args)
        {
            using (var cmd = this.Factory.CreateCommand())
            {
                //  准备参数信息
                CommandInfo cmdInfo = CommandInfo.Create(this.Factory, format, args);

                //  更新参数信息
                cmdInfo.UpdateCommand(cmd, this.Connection, this.Transaction);

                //  执行SQL语句
                return cmd.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// (支持异步或线程)执行 SQL 语句，返回受影响行数
        /// </summary>
        /// <param name="format"> 复合格式字符串 </param>
        /// <param name="args"> 一个对象数组，其中包含零个或多个要设置格式的对象 </param>
        /// <returns></returns>
        public int ExecuteNonQueryFormatAsync(string format, params object[] args)
        {
            //  创建连接
            using (var connection = this.Factory.CreateConnection(this.ConnectionString))
            {
                //  创建命令对象
                using (var cmd = this.Factory.CreateCommand())
                {
                    //  准备参数信息
                    CommandInfo cmdInfo = CommandInfo.Create(this.Factory, format, args);

                    //  更新参数信息
                    cmdInfo.UpdateCommand(cmd, connection);

                    //  打开连接
                    connection.Open();

                    //  执行SQL语句
                    return cmd.ExecuteNonQuery();
                }
            }
        }

        #endregion

        #region ExecuteScalar(执行 SQL 查询，并返回查询所返回的结果集中第一行的第一列)

        /// <summary>
        /// 执行 SQL 查询，并返回查询所返回的结果集中第一行的第一列
        /// </summary>
        /// <param name="cmdText"> 命令文本 </param>
        /// <param name="cmdType"> 命令类型 </param>
        /// <param name="parameters"> 参数列表 </param>
        /// <returns></returns>
        public object ExecuteScalar(string cmdText, CommandType cmdType, params IDataParameter[] parameters)
        {
            //  创建命令对象
            using (var cmd = this.Factory.CreateCommand())
            {
                //  准备参数信息
                CommandInfo cmdInfo = CommandInfo.Create(cmdType, cmdText, parameters);

                //  更新参数信息
                cmdInfo.UpdateCommand(cmd, this.Connection, this.Transaction);

                //  执行SQL语句
                return cmd.ExecuteScalar();
            }
        }

        /// <summary>
        /// 执行 SQL 查询，并返回查询所返回的结果集中第一行的第一列
        /// </summary>
        /// <param name="format"> 复合格式字符串 </param>
        /// <param name="args"> 一个对象数组，其中包含零个或多个要设置格式的对象 </param>
        /// <returns></returns>
        public object ExecuteScalarFormat(string format, params object[] args)
        {
            //  创建命令对象
            using (var cmd = this.Factory.CreateCommand())
            {
                //  准备参数信息
                CommandInfo cmdInfo = CommandInfo.Create(this.Factory, format, args);

                //  更新参数信息
                cmdInfo.UpdateCommand(cmd, this.Connection, this.Transaction);

                //  执行SQL语句
                return cmd.ExecuteScalar();
            }
        }

        /// <summary>
        /// (支持异步或线程)执行 SQL 查询，并返回查询所返回的结果集中第一行的第一列
        /// </summary>
        /// <param name="format"> 复合格式字符串 </param>
        /// <param name="args"> 一个对象数组，其中包含零个或多个要设置格式的对象 </param>
        /// <returns></returns>
        public object ExecuteScalarFormatAsync(string format, params object[] args)
        {
            //  创建新连接
            using (var connection = this.Factory.CreateConnection(this.ConnectionString))
            {
                //  创建命令对象
                using (var cmd = this.Factory.CreateCommand())
                {
                    //  准备参数信息
                    CommandInfo cmdInfo = CommandInfo.Create(this.Factory, format, args);

                    //  更新参数信息
                    cmdInfo.UpdateCommand(cmd, connection);

                    //  打开连接
                    connection.Open();

                    //  执行SQL语句
                    return cmd.ExecuteScalar();
                }
            }
        }

        #endregion

        #region ExecuteReader

        /// <summary>
        /// 
        /// </summary>
        /// <param name="cmdText"> 命令文本 </param>
        /// <param name="cmdType"> 命令类型 </param>
        /// <param name="parameters"> 参数列表 </param>
        /// <returns></returns>
        public IDataReader ExecuteReader(string cmdText, CommandType cmdType, params IDataParameter[] parameters)
        {
            //  创建命令对象
            using (var cmd = this.Factory.CreateCommand())
            {
                //  准备参数信息
                CommandInfo cmdInfo = CommandInfo.Create(cmdType, cmdText, parameters);

                //  更新参数信息
                cmdInfo.UpdateCommand(cmd, this.Connection, this.Transaction);

                //  执行SQL语句
                return cmd.ExecuteReader();
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="format"> 复合格式字符串 </param>
        /// <param name="args"> 一个对象数组，其中包含零个或多个要设置格式的对象 </param>
        /// <returns></returns>
        public IDataReader ExecuteReaderFormat(string format, params object[] args)
        {
            //  创建命令对象
            using (var cmd = this.Factory.CreateCommand())
            {
                //  准备参数信息
                CommandInfo cmdInfo = CommandInfo.Create(this.Factory, format, args);

                //  更新参数信息
                cmdInfo.UpdateCommand(cmd, this.Connection, this.Transaction);

                //  执行SQL语句
                return cmd.ExecuteReader();
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="format"> 复合格式字符串 </param>
        /// <param name="args"> 一个对象数组，其中包含零个或多个要设置格式的对象 </param>
        /// <returns></returns>
        public IDataReader ExecuteReaderFormatAsync(string format, params object[] args)
        {
            //  创建新的连接
            var connection = this.Factory.CreateConnection(this.ConnectionString);
            {
                try
                {
                    //  创建命令对象
                    using (var cmd = this.Factory.CreateCommand())
                    {
                        //  准备参数信息
                        CommandInfo cmdInfo = CommandInfo.Create(this.Factory, format, args);

                        //  更新参数信息
                        cmdInfo.UpdateCommand(cmd, connection);

                        //  打开连接
                        connection.Open();

                        //  执行SQL语句
                        return cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    }
                }
                catch (Exception ex)
                {
                    if (connection != null)
                    {
                        using (connection)
                        {
                            if (connection.State != ConnectionState.Closed)
                                connection.Close();
                            connection.Dispose();
                        }
                    }
                    throw ex;
                }

            }
        }

        #endregion

        #region FillDataTable

        /// <summary>
        /// 填充数据源
        /// </summary>
        /// <param name="source"> 数据源 </param>
        /// <param name="cmdText"> 查询命令文本 </param>
        /// <param name="cmdType"> 命命类型 </param>
        /// <param name="parameters"> 参数列表 </param>
        /// <returns></returns>
        public int FillDataTable(System.Data.DataTable source, string cmdText, CommandType cmdType = CommandType.Text, params  IDataParameter[] parameters)
        {
            //  创建适配器对象
            using (var adapter = this.Factory.CreateDataAdapter(cmdText, this.Connection))
            {
                //  如果数据源为空，则实例化新的数据源
                if (source == null)
                    source = new DataTable();
                //  创建参数对象
                var cmdInfo = CommandInfo.Create(cmdType, cmdText, parameters);
                //  创建查询命令对象
                if (adapter.SelectCommand == null)
                    adapter.SelectCommand = (System.Data.Common.DbCommand)this.Factory.CreateCommand();
                //  更新
                cmdInfo.UpdateCommand(adapter.SelectCommand, this.Connection, this.Transaction);
                //  填充数据
                return adapter.Fill(source);
            }
        }

        /// <summary>
        /// 填充数据源
        /// </summary>
        /// <param name="source"> 数据源 </param>
        /// <param name="format"> 格式代命令文本 </param>
        /// <param name="args"> 一个对象数组，其中包含零个或多个要设置格式的对象 </param>
        /// <returns></returns>
        public int FillDataTableFormat(DataTable source, string format, params object[] args)
        {
            //  创建适配器
            using (var adapter = this.Factory.CreateDataAdapter(null, this.Connection))
            {
                if (source == null)
                    source = new DataTable();
                //  创建参数对象
                var cmdInfo = CommandInfo.Create(this.Factory, format, args);
                //  创建查询命令对象
                if (adapter.SelectCommand == null)
                    adapter.SelectCommand = (System.Data.Common.DbCommand)this.Factory.CreateCommand();
                //  更新
                cmdInfo.UpdateCommand(adapter.SelectCommand, this.Connection, this.Transaction);
                //  填充数据
                return adapter.Fill(source);
            }
        }

        /// <summary>
        /// 填充数据源
        /// </summary>
        /// <param name="source"> 数据源 </param>
        /// <param name="fromat"> 格式代命令文本 </param>
        /// <param name="args"> 一个对象数组，其中包含零个或多个要设置格式的对象 </param>
        /// <returns></returns>
        public int FillDataTableFormatAsync(DataTable source, string fromat, params object[] args)
        {
            CommandInfo cmdInfo = CommandInfo.Create(this.Factory, fromat, args);
            using (var adapter = this.Factory.CreateDataAdapter(cmdInfo.CommandText, this.ConnectionString))
            {
                if (source == null)
                    source = new DataTable();
                cmdInfo.UpdateCommand(adapter.SelectCommand);
                return adapter.Fill(source);
            }
        }

        #endregion

        #region FillDataSet

        /// <summary>
        /// 
        /// </summary>
        /// <param name="source"></param>
        /// <param name="cmdText"></param>
        /// <param name="cmdType"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public int FillDataSet(System.Data.DataSet source, string cmdText, CommandType cmdType = CommandType.Text, params  IDataParameter[] parameters)
        {
            //  创建参数对象
            var cmdInfo = CommandInfo.Create(cmdType, cmdText, parameters);
            //  
            using (var adapter = this.Factory.CreateDataAdapter(cmdInfo.CommandText, this.Connection))
            {
                //  
                if (source == null)
                    source = new DataSet();
                //  创建查询命令对象
                if (adapter.SelectCommand == null)
                    adapter.SelectCommand = (System.Data.Common.DbCommand)this.Factory.CreateCommand();
                //  更新
                cmdInfo.UpdateCommand(adapter.SelectCommand, this.Connection, this.Transaction);
                //  填充数据
                return adapter.Fill(source);
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="source"></param>
        /// <param name="format"></param>
        /// <param name="args"></param>
        /// <returns></returns>
        public int FillDataSetFormat(DataSet source, string format, params object[] args)
        {
            //  创建参数对象
            var cmdInfo = CommandInfo.Create(this.Factory, format, args);
            //  
            using (var adapter = this.Factory.CreateDataAdapter(cmdInfo.CommandText, this.Connection))
            {
                if (source == null)
                    source = new DataSet();
                //  创建查询命令对象
                adapter.SelectCommand = (System.Data.Common.DbCommand)this.Factory.CreateCommand();
                //  更新
                cmdInfo.UpdateCommand(adapter.SelectCommand, this.Connection, this.Transaction);
                //  填充数据
                return adapter.Fill(source);
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="source"></param>
        /// <param name="format"></param>
        /// <param name="args"></param>
        /// <returns></returns>
        public int FillDataSetFormatAsync(DataSet source, string format, params object[] args)
        {
            CommandInfo cmdInfo = CommandInfo.Create(this.Factory, format, args);
            using (var adapter = this.Factory.CreateDataAdapter(cmdInfo.CommandText, this.ConnectionString))
            {
                if (source == null)
                    source = new DataSet();
                cmdInfo.UpdateCommand(adapter.SelectCommand);
                return adapter.Fill(source);
            }
        }

        #endregion

        #region Open/Close

        /// <summary>
        /// 打开数据库连接
        /// </summary>
        /// <returns></returns>
        public System.Collections.Generic.KeyValuePair<bool, string> Open()
        {
            //  判断数据库连接对象,如果为null,则创建新的实例
            if (this.Connection == null)
                this.Connection = this.Factory.CreateConnection(this.ConnectionString);
            //  判断数据库连接对象,如果为null,则返回失败
            if (this.Connection == null)
                return new System.Collections.Generic.KeyValuePair<bool, string>(false, "创建数据库连接失败!");
            //  定义返回值
            var rValue = new System.Collections.Generic.KeyValuePair<bool, string>(true, string.Empty);
            try
            {
                //  如果连接状态不为Open,则关闭连接再重新打开连接
                if (this.Connection.State != ConnectionState.Open)
                {
                    //  如果连接状态不为Closed,则关闭连接
                    if (this.Connection.State != ConnectionState.Closed)
                        this.Connection.Close();
                    //  打开数据库连接
                    this.Connection.Open();
                }
                //  判断数据库连接状态是否为Open,返回值
                rValue = new System.Collections.Generic.KeyValuePair<bool, string>(
                    this.Connection.State == ConnectionState.Open,
                    (this.Connection.State == ConnectionState.Open) ? "数据库连接成功!" : "数据库连接失败!");
            }
            catch (Exception ex)
            {
                //  捕获异常，作为返回值
                rValue = new System.Collections.Generic.KeyValuePair<bool, string>(false, ex.Message);
            }
            return rValue;
        }

        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        /// <param name="dispose">是否释放连接</param>
        /// <returns></returns>
        public System.Collections.Generic.KeyValuePair<bool, string> Close(bool dispose = false)
        {
            if (this.Connection == null)
                return new System.Collections.Generic.KeyValuePair<bool, string>(true, string.Empty);
            else
            {
                //  如果连接状态未关闭,则手动关闭连接
                if (this.Connection.State != ConnectionState.Closed)
                    this.Connection.Close();
                if (dispose)
                {
                    using (this.Connection)
                    {
                        this.Connection.Dispose();
                        this.Connection = null;
                    }
                }
                //  获取是否关闭成功
                bool isClose = this.Connection == null || this.Connection.State == ConnectionState.Closed;
                //  返回操作结果
                return new System.Collections.Generic.KeyValuePair<bool, string>(isClose, isClose ? "数据库连接关闭成功!" : "数据库连接关闭失败!");
            }
        }

        #endregion

        #region BeginTransaction/Rollback/Commit

        /// <summary>
        /// 开始数据库事务
        /// </summary>
        /// <returns></returns>
        public System.Collections.Generic.KeyValuePair<bool, string> BeginTransaction()
        {
            return this.BeginTransaction(null);
        }

        /// <summary>
        /// 开始数据库事务
        /// </summary>
        /// <param name="level">事务锁级别</param>
        /// <returns></returns>
        public System.Collections.Generic.KeyValuePair<bool, string> BeginTransaction(IsolationLevel? level)
        {
            //  如果数据库连接对象为null,或连接对象状态不为Open,则返回失败
            if (this.Connection == null)
                return new System.Collections.Generic.KeyValuePair<bool, string>(false, "数据库连接未初始化");
            else if (this.Connection.State != ConnectionState.Open)
                return new System.Collections.Generic.KeyValuePair<bool, string>(false, "数据库连接未打开");
            else if (this.Transaction != null)
                return new System.Collections.Generic.KeyValuePair<bool, string>(false, "已存在数据库事务!");
            try
            {
                //  开始数据库事务
                this.Transaction = level.HasValue ? this.Connection.BeginTransaction(level.Value) : this.Connection.BeginTransaction();
                //  返回事务开启操作结果
                return new System.Collections.Generic.KeyValuePair<bool, string>(
                    this.Transaction != null,
                    (this.Transaction != null) ? "数据库事务开启成功" : "数据库事务开启失败");
            }
            catch (Exception ex)
            {
                //  捕获异常，作为返回值
                return new System.Collections.Generic.KeyValuePair<bool, string>(false, ex.Message);
            }
        }

        /// <summary>
        /// 数据库事务回滚
        /// </summary>
        /// <returns></returns>
        public System.Collections.Generic.KeyValuePair<bool, string> Rollback()
        {
            //  如果数据库事务对象为null,则返回失败
            if (this.Transaction == null)
                return new System.Collections.Generic.KeyValuePair<bool, string>(false, "数据库事务处理未初始化");
            try
            {
                using (this.Transaction)
                {
                    //  事务回滚
                    this.Transaction.Rollback();
                    //  释放事务资源
                    this.Transaction.Dispose();
                    //  清空事务
                    this.Transaction = null;
                }
                //  返回事务提交操作结果
                return new System.Collections.Generic.KeyValuePair<bool, string>(true, "数据库事务回滚成功");
            }
            catch (Exception ex)
            {
                //  捕获异常，作为返回值
                return new System.Collections.Generic.KeyValuePair<bool, string>(false, ex.Message);
            }
        }

        /// <summary>
        /// 数据库事务提交
        /// </summary>
        /// <returns></returns>
        public System.Collections.Generic.KeyValuePair<bool, string> Commit()
        {
            //  如果数据库事务对象为null,则返回失败
            if (this.Transaction == null)
                return new System.Collections.Generic.KeyValuePair<bool, string>(false, "数据库事务处理未初始化");
            try
            {
                using (this.Transaction)
                {
                    //  提交事务
                    this.Transaction.Commit();
                    //  释放事务资源
                    this.Transaction.Dispose();
                    //  清空事务
                    this.Transaction = null;
                }
                //  返回事务提交操作结果
                return new System.Collections.Generic.KeyValuePair<bool, string>(true, "数据库事务提交成功");
            }
            catch (Exception ex)
            {
                //  捕获异常，作为返回值
                return new System.Collections.Generic.KeyValuePair<bool, string>(false, ex.Message);
            }
        }

        #endregion

        #region Schema

        /// <summary>
        /// 
        /// </summary>
        /// <param name="collectionName"></param>
        /// <param name="restrictionValues"></param>
        /// <returns></returns>
        public System.Data.DataTable GetSchema(string collectionName = null, params string[] restrictionValues)
        {
            return this.Factory.GetSchema(this.Connection, collectionName, restrictionValues);
        }

        /// <summary>
        /// 获取所有表
        /// </summary>
        /// <returns></returns>
        public System.Data.DataTable GetTables()
        {
            return this.Factory.GetTables(this.Connection);
        }

        /// <summary>
        /// 获取所有列
        /// </summary>
        /// <returns></returns>
        public System.Data.DataTable GetColumns()
        {
            return this.Factory.GetColumns(this.Connection);
        }

        /// <summary>
        /// 获取索引列
        /// </summary>
        /// <returns></returns>
        public System.Data.DataTable GetIndexColumns()
        {
            return this.Factory.GetIndexColumns(this.Connection);
        }

        /// <summary>
        /// 获取所有试图
        /// </summary>
        /// <returns></returns>
        public System.Data.DataTable GetViews()
        {
            return this.Factory.GetViews(this.Connection);
        }

        /// <summary>
        /// 获取所有数据类型
        /// </summary>
        /// <returns></returns>
        public System.Data.DataTable GetDataTypes()
        {
            return this.Factory.GetDataTypes(this.Connection);
        }

        /// <summary>
        /// 获取服务器时间
        /// </summary>
        /// <returns></returns>
        public System.DateTime GetDateTime()
        {
            if (!string.IsNullOrEmpty(this.Factory.GetDateTimeSql))
                return (System.DateTime)this.ExecuteScalar(this.Factory.GetDateTimeSql, CommandType.Text);
            else
                return DateTime.Now;
        }

        /// <summary>
        /// 获取数据库扩展属性
        /// </summary>
        /// <returns></returns>
        public System.Data.DataTable GetExtended_Properties()
        {
            System.Data.DataTable source = new DataTable();
            if (this.Factory.DatabaseType == DatabaseType.SqlServer)
            {
                string sql = @"select OBJECT_NAME(t1.major_id) TableName,
       t2.name ColumnName,
       t1.name,
       t1.value
  from sys.extended_properties t1
  left join sys.columns t2
    on t2.object_id = t1.major_id
   and t2.column_id = t1.minor_id
";
                this.FillDataTableFormatAsync(source, sql);
            }
            return source;
        }

        #endregion
    }
}
